﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Common;

namespace DAL.ETL
{
    public class CitationParserDALC
    {
        private const string SaveResultProcudureName = "CitationParserResultInsert";

        public static IDataReader GetCitationText(
            string connName, string sql)
        {
            string connStr = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
            using (SqlConnection conn=new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                SqlDataReader reader = cmd.ExecuteReader();
                return reader;
            }
        }

        public static void CreateProcedureToSaveParserResult(
            string connName, string outputTableName,
            string srcKeyField, string pubTimeField,
            string journalNameField, string bookTitleField,
            string authorField, string volumeField,
            string issueField, string pageField)
        {
            string connStr = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
            List<TableColumn> cols=new List<TableColumn>();
            cols.Add(new TableColumn{ColumnName=srcKeyField,DbType=SqlDbType.Int});
            cols.Add(new TableColumn{ColumnName=pubTimeField,DbType=SqlDbType.VarChar,Size=20});
            cols.Add(new TableColumn {ColumnName = journalNameField, DbType = SqlDbType.VarChar, Size = 255});
            cols.Add(new TableColumn {ColumnName = bookTitleField, DbType = SqlDbType.VarChar, Size = 255});
            cols.Add(new TableColumn {ColumnName = authorField, DbType = SqlDbType.VarChar, Size = 255});
            cols.Add(new TableColumn {ColumnName = volumeField, DbType = SqlDbType.VarChar, Size = 100});
            cols.Add(new TableColumn {ColumnName = issueField, DbType = SqlDbType.VarChar, Size = 100});
            cols.Add(new TableColumn {ColumnName = pageField, DbType = SqlDbType.VarChar, Size = 100});
            string spBody = StoredProcUtil.CreateStoredProcedureStatementForInsert(
                DataProviderType.MSSQL, SaveResultProcudureName, "dbo", outputTableName, cols.ToArray());
            DbUtil.NewStoredProc(connStr, SaveResultProcudureName, spBody);
        }

        public static void SaveParserResult(
            string connName,
            string srcKeyField, int srcKey, 
            string pubTimeField, string pubTime,
            string journalNameField, string journalName, 
            string bookTitleField, string bookTitleOrMeetingName,
            string titleField, string title,
            string authorField, string authors, 
            string volumeField, string volume, 
            string issueField, string issue, 
            string pageField, string pages)
        {
            string connStr = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = SaveResultProcudureName;
                cmd.Parameters.Clear();
                
                cmd.Parameters.AddWithValue(srcKeyField,srcKey);
                
                if (pubTime != null)
                    cmd.Parameters.AddWithValue(pubTimeField, pubTime);
                else
                    cmd.Parameters.AddWithValue(pubTimeField, DBNull.Value);
                
                if (journalName != null)
                    cmd.Parameters.AddWithValue(journalNameField, journalName);
                else
                    cmd.Parameters.AddWithValue(journalNameField, DBNull.Value);
                
                if (bookTitleOrMeetingName != null)
                    cmd.Parameters.AddWithValue(bookTitleField, bookTitleOrMeetingName);
                else
                    cmd.Parameters.AddWithValue(bookTitleField, DBNull.Value);

                if (title != null)
                    cmd.Parameters.AddWithValue(titleField, title);
                else
                    cmd.Parameters.AddWithValue(titleField, DBNull.Value);
                
                if (authors != null)
                    cmd.Parameters.AddWithValue(authorField, authors);
                else
                    cmd.Parameters.AddWithValue(authorField, DBNull.Value);
                
                if (volume != null)
                    cmd.Parameters.AddWithValue(volumeField, volume);
                else
                    cmd.Parameters.AddWithValue(volumeField, DBNull.Value);
                
                if (issue != null)
                    cmd.Parameters.AddWithValue(issueField, issue);
                else
                    cmd.Parameters.AddWithValue(issueField, DBNull.Value);
                
                if (pages != null)
                    cmd.Parameters.AddWithValue(pageField, pages);
                else
                    cmd.Parameters.AddWithValue(pageField, DBNull.Value);
               
                cmd.ExecuteNonQuery();
            }
        }
    }
}
